<?
require_once('./lib/authentication.lib.php');
require_once('./lib/navigation.lib.php');
?>

<HTML>
<HEAD><TITLE>Avg crowd</TITLE>
<link rel="stylesheet" href="css/style.css" type="text/css">
<SCRIPT TYPE="text/javascript" SRC="js/game.js"></SCRIPT>
<SCRIPT TYPE="text/javascript" SRC="js/sorttable.js"></SCRIPT>
<STYLE TYPE="text/css">
<!--
.table_head
   {
   color: white;
   background-color: darkblue;
   }
.table_line
   {
   background-color: lightgrey;
   }
-->
</STYLE>
</HEAD>

<BODY>

<?
$championship = mysql_escape_string($_GET{"id"});
echo "<SCRIPT>\n<!--\nChamp = $championship;\n-->\n</SCRIPT>\n";
?>

<? echo foot_header($championship, "crowd"); ?>


<?
$query = "SELECT * FROM Championship WHERE id = '$championship';";
$result_champ = mysql_query($query);

$num = mysql_numrows($result_champ);

if ($num != 1)
{
	echo "<H1>Invalid championship $championship</H1>";
}

$id = mysql_result($result_champ, 0, "id");
$name = mysql_result($result_champ, 0, "name");
$begin = mysql_result($result_champ, 0, "begin");
$end = mysql_result($result_champ, 0, "end");

$begin = substr($begin, 0, 4);
$end = substr($end, 0, 4);
echo "<h1>$name $begin" . (($begin != $end) ? "/$end" : "") . "</h1>\n";

$query = "SELECT Phase.id FROM Phase where `championship` = $id ORDER BY `order`;";
$result_phase = mysql_query($query);
$num_phase = mysql_numrows($result_phase);
$phases = "''";
for ($j = 0; $j < $num_phase; $j++)
{
	$id_phase = mysql_result($result_phase, $j, "Phase.id");
	$phases = "$phases, '$id_phase'";
}

$query = "SELECT DISTINCT Team.id FROM Championship JOIN Phase ON Phase.championship = Championship.id JOIN `Group` ON `Group`.phase = Phase.id JOIN team_group ON `Group`.id = team_group.group JOIN Team ON team_group.team = Team.id WHERE Championship.id = '$id';";
$result_team = mysql_query($query);
$num_team = mysql_numrows($result_team);
$teams = "''";
for ($j = 0; $j < $num_team; $j++)
{
	$id_team = mysql_result($result_team, $j, "Team.id");
	$teams = "$teams, '$id_team'";
}

$query = "SELECT Team.id, Team.name, avg(attendance) crowd, max(attendance) max_c, min(attendance) min_c, sum(attendance) sum_c, count(Game.id) games FROM Team LEFT JOIN Game ON home = Team.id AND phase IN ($phases) AND attendance != 0 AND played = 'played' WHERE Team.id IN ($teams) GROUP BY Team.id ORDER BY crowd DESC, name ASC;";
$result_team = mysql_query($query);
$num_team = mysql_numrows($result_team);

echo "<TABLE class='sortable indexed' id='crowd_table'>\n";
echo "<TR class=table_head><TH align='left'>Team</TH><TH>Avg Crowd</TH><TH>Max Crowd</TH><TH>Min Crowd</TH><TH>Total</TH><TH>Games</TH></TR>\n";
for ($j = 0; $j < $num_team; $j++)
{
	$team_id = mysql_result($result_team, $j, "Team.id");
	$team = mysql_result($result_team, $j, "Team.name");
	$crowd = mysql_result($result_team, $j, "crowd");
	$max_c = mysql_result($result_team, $j, "max_c");
	$min_c = mysql_result($result_team, $j, "min_c");
	$sum_c = mysql_result($result_team, $j, "sum_c");
	$games = mysql_result($result_team, $j, "games");

	$crowd = number_format($crowd, 0, ",", ".");
	$max_c = number_format($max_c, 0, ",", ".");
	$min_c = number_format($min_c, 0, ",", ".");
	$sum_c = number_format($sum_c, 0, ",", ".");

	echo "<TR class=table_line><TD><A HREF='team.php?champ=$championship&team=$team_id'>$team</A></TD><TD align='right'>$crowd</TD><TD align='right'>$max_c</TD><TD align='right'>$min_c</TD><TD align='right'>$sum_c</TD><TD align='right'>$games</TD></TR>\n";
}
echo "</TABLE>\n";
?>

<? echo foot_footer($championship, "crowd"); ?>

</BODY>
</HTML>
